﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_Update_CurrentDbFileNames]
#-- Purpose:		Store what the current files of a database are.  Used in
#--					restore stored procedures.
#--	Last Update:	10/23/2015
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_Update_CurrentDbFileNames]
(
	@database_name varchar(256)
)
AS

SET NOCOUNT ON;

--- Declare Local Variables
DECLARE @sCmd varchar(MAX)

--- Initialize Local Variables
IF DB_ID(@database_name) IS NOT NULL
	SET		@sCmd = '
MERGE [dbo].[tblRest_FileNames] AS Target
USING (
	SELECT		DB_NAME(mf.[database_id]) [database_name],
				ds.[name] [filegroup_name],
				mf.[file_id] [file_number],
				mf.[name] [logical_name],
				[dbo].[fnIO_DirectoryName]([physical_name]) + ''\'' [directory_name],
				REPLACE([physical_name], [dbo].[fnIO_DirectoryName]([physical_name]) + ''\'', '''') [file_name],
				(CASE mf.[type] WHEN 0 THEN ''D'' WHEN 1 THEN ''L'' WHEN 4 THEN ''F'' ELSE NULL END) [file_type],
				[dbo].[fnIO_DirectoryName]([physical_name]) + ''\'' + REPLACE([physical_name], [dbo].[fnIO_DirectoryName]([physical_name]) + ''\'', '''') [physical_name]
	FROM		[sys].[master_files] mf
	LEFT JOIN	[[VAR_DBNAME]].[sys].[data_spaces] ds ON mf.[data_space_id] = ds.[data_space_id]
	WHERE		DB_NAME(mf.[database_id]) = ''[VAR_DBNAME]''
) AS Source ON Target.[database_name] = Source.[database_name]
			AND Target.[file_number] = Source.[file_number]

WHEN Matched THEN
	UPDATE SET	[filegroup_name] = Source.[filegroup_name],
				[logical_name] = Source.[logical_name],
				[directory_name] = Source.[directory_name],
				[file_name] = Source.[file_name],
				[file_type] = Source.[file_type],
				[physical_name] = Source.[physical_name]

WHEN Not Matched BY Target THEN
	INSERT (
		[database_name],
		[file_number],
		[filegroup_name],
		[logical_name],
		[directory_name],
		[file_name],
		[file_type],
		[physical_name]
	)
	VALUES (
		Source.[database_name],
		Source.[file_number],
		Source.[filegroup_name],
		Source.[logical_name],
		Source.[directory_name],
		Source.[file_name],
		Source.[file_type],
		Source.[physical_name]
	)

WHEN Not Matched BY Source AND Target.[database_name] = ''[VAR_DBNAME]'' THEN DELETE;
'
ELSE
	DELETE FROM [dbo].[tblRest_FileNames] WHERE [database_name] = @database_name

--- Get the current files of a database
IF [dbo].[fnConfig_IsDatabaseOffline](@database_name) = 0
  BEGIN
	SELECT	@sCmd = REPLACE(@sCmd, '[VAR_DBNAME]', @database_name)
	EXEC (@sCmd)
  END
GO